# -*- coding: utf-8 -*-
class SaleOrderController < ApplicationController
  before_filter :employee_authorize,:except=>[:sale_order_print_header,:sale_order_print_content,:sale_order_print_footer]
  before_filter :sys_before_log
  before_filter :employee_action_hx
  before_filter :privilege_filter,:except=>[:editSaleOrderAfterAudit,:hxListForSale,:commonSalesStats,:setupPurchaseStatus,:sale_order_print_header,:sale_order_print_content,:sale_order_print_footer,:returnedForSaleList,:getDeptSalesForReport,:deptSalesReport,:salesSalesReport,:getSalesSalesForReport,:productSalesVolumeReport,:productSalesAmountReport,:getProductSalesVolumeForReport,:getProductSalesAmountForReport,:productNeedDelivery,:getSalesStrategyAmountForReport,:salesStrategyAmountReport,:exportDeliveryOrderToXLS,:salesStrategyResidualPayment,:getSumResidualPaymentConsumeByCustomerIdStrategyId]

  #销售订单列表
  def saleOrderList

  end

  #销售订单审批页面
  def saleOrderAuditList

  end
  
  #可出库销售订单列表
  def saleOrderForDeliveryList
    
  end

  #新增销售订单页面
  def newSaleOrder
    
  end

  #编辑销售订单页面
  def editSaleOrder
    
  end

  #审批后，再次编辑销售订单
  def editSaleOrderAfterAudit
    
  end

  #审批销售订单
  def auditSaleOrder
    
  end

  #显示销售订单转为出库订单的转换设置页面。
  def deliverySaleOrder
    
  end

  #显示销售订单转换为的出库订单列表
  def deliveryForSaleList
    
  end

  #显示审批撤消的页面
  def auditSaleOrderForCancel
    
  end

  #显示审批更改销售订单的页面
  def auditSaleOrderForEdit
    
  end

  #销售统计
  def analysis
    
  end

  
  def getAllSaleOrders
    begin
      conditionsHash = Hash.new
      paramsHash = Hash.new
      paramsHash[:offset] = params[:start].to_i
      paramsHash[:limit] = params[:limit].to_i
      
      conditionsStr = ''

      if params[:order_number].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'seq_number in (:order_number)'
        else
          conditionsStr = conditionsStr+' AND seq_number in (:order_number)'
        end
        conditionsHash[:order_number] = params[:order_number].split(/,/)
      end

      if params[:auditing_status].to_s != '' && params[:auditing_status] != ' '
        if conditionsStr == ''
          conditionsStr = 'auditing_status like :auditing_status'
        else
          conditionsStr = conditionsStr+' AND auditing_status like :auditing_status'
        end
        conditionsHash[:auditing_status] = '%'+params[:auditing_status].to_s+'%'
      elsif params[:auditing_status] == ' '
        if conditionsStr == ''
          conditionsStr = 'auditing_status = :auditing_status'
        else
          conditionsStr = conditionsStr+' AND auditing_status = :auditing_status'
        end
        conditionsHash[:auditing_status] = ''
      end

      if params[:customer_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'customer_id = :customer_id'
        else
          conditionsStr = conditionsStr+' AND customer_id = :customer_id'
        end
        conditionsHash[:customer_id] = params[:customer_id].to_i
      end

      #销售员
      if params[:sales_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'sales_id = :sales_id'
        else
          conditionsStr = conditionsStr+' AND sales_id = :sales_id'
        end
        conditionsHash[:sales_id] = params[:sales_id].to_i
      end

      if params[:begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'action_date >= :begin_date'
        else
          conditionsStr = conditionsStr+' AND action_date >= :begin_date'
        end
        conditionsHash[:begin_date] = params[:begin_date].to_date
      end

      if params[:end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'action_date <= :end_date'
        else
          conditionsStr = conditionsStr+' AND action_date <= :end_date'
        end
        conditionsHash[:end_date] = params[:end_date].to_date
      end

      if params[:c_begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'sale_orders.created_at >= :created_at_begin'
        else
          conditionsStr = conditionsStr+' AND sale_orders.created_at >= :created_at_begin'
        end
        conditionsHash[:created_at_begin] = params[:c_begin_date].to_date.to_s + ' 00:00:00'
      end

      if params[:c_end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'sale_orders.created_at <= :created_at_end'
        else
          conditionsStr = conditionsStr+' AND sale_orders.created_at <= :created_at_end'
        end
        conditionsHash[:created_at_end] = params[:c_end_date].to_date.to_s + ' 59:59:59'
      end

      #显示产品
      if params[:product_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'sale_order_details.product_id = :product_id'
        else
          conditionsStr = conditionsStr+' AND sale_order_details.product_id = :product_id'
        end
        conditionsHash[:product_id] = params[:product_id].to_i
      end

      if params[:sell_dept_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = 'sell_dept_id = :sell_dept_id'
        else
          conditionsStr = conditionsStr+' AND sell_dept_id = :sell_dept_id'
        end
        conditionsHash[:sell_dept_id] = params[:sell_dept_id].to_i
      end

      
      #根据角色判断，是读取所有销售记录，还是和本部门相关的销售记录
      role = Role.find(session[:role_id])
      if role.cross_dept_sale_order_query == "否"
        if conditionsStr == ''
          conditionsStr = 'department_id = :department_id'
        else
          conditionsStr = conditionsStr+' AND department_id = :department_id'
        end
        conditionsHash[:department_id] = session[:department_id]
      end
      if role.cross_company == "否"
        if conditionsStr == ''
          conditionsStr = 'sale_orders.company_id = :company_id'
        else
          conditionsStr = conditionsStr+' AND sale_orders.company_id = :company_id'
        end
        conditionsHash[:company_id] = session[:company_id]
      end
      count = SaleOrder.countAllSaleOrders conditionsStr,conditionsHash
      saleOrders = SaleOrder.findAllSaleOrders conditionsStr,conditionsHash,paramsHash

      if session[:financial_management] == "是"
        jsonStr="{totalProperty:"+count.to_s+",root:"+saleOrders.to_json(:include=>{:company=>{:only=>[:id,:name]},:employee=>{:only=>[:id,:name]},:department=>{:only=>[:id,:name]},:sale_returneds=>{:only=>[:id]},:sell_department=>{:only=>[:id,:name]},:customer=>{:only=>[:id,:name]},:auditor=>{:only=>[:id,:name]},:delivery_employee=>{:only=>[:id,:name]},:sales=>{:only=>[:id,:name]},:sale_type=>{:only=>[:id,:name]},:sales_strategy=>{:only=>[:id,:name]}})+"}"

      else
        jsonStr="{totalProperty:"+count.to_s+",root:"+saleOrders.to_json(:except=>[:amount,:rec_amount],:include=>{:company=>{:only=>[:id,:name]},:employee=>{:only=>[:id,:name]},:department=>{:only=>[:id,:name]},:sale_returneds=>{:only=>[:id]},:sell_department=>{:only=>[:id,:name]},:customer=>{:only=>[:id,:name]},:auditor=>{:only=>[:id,:name]},:delivery_employee=>{:only=>[:id,:name]},:sales=>{:only=>[:id,:name]},:sale_type=>{:only=>[:id,:name]},:sales_strategy=>{:only=>[:id,:name]}})+"}"

      end
#logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end

  #得到某一个采购订单的信息
  def getSaleOrderById
    saleOrder = SaleOrder.where(id:params[:id]).includes([:customer,:sell_department,:sales,:sale_type]).first
    rootStr = saleOrder.to_json(:include=>{:customer=>{:only=>[:id,:name]},:employee=>{:only=>[:id,:name]},:sell_department=>{:only=>[:id,:name]},:sales=>{:only=>[:id,:name]},:sale_type=>{:only=>[:id,:name,:discount]},:sales_strategy=>{:only=>[:id,:name]}})
    jsonStr = "{root:["+rootStr+"]}"
    render :text=>jsonStr, :layout=>false
  end
  
  def getSaleOrderDetailsById
    saleOrderDetails = SaleOrderDetail.where("sale_order_id = ? and is_locked = 0",params[:sale_order_id]).includes([:product])
    jsonStr="{totalProperty:"+saleOrderDetails.length.to_s+",root:"+saleOrderDetails.to_json(:include=>{:product=>{:only=>[:id,:name,:is_suit,:spec,:number,:unit]}})+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #得到被锁定的销售订单详细项
  #现在这个方法变动了一些，以前是仅取变更后的，现在是求的变更前后的交集。
  def getLockedSaleOrderDetailsById
    oldSaleOrderDetails = SaleOrderDetail.where("sale_order_id = ? and is_locked = 0",params[:sale_order_id]).includes([:product])

    newSaleOrderDetails = SaleOrderDetail.where("sale_order_id = ? and is_locked = 1",params[:sale_order_id]).includes([:product])

    #求变化的记录集
    difSaleOrderDetails = Array.new
    oldSaleOrderDetails.each do |oldSaleOrderDetail|
      #第一层循环主要是找出编辑前有，编辑后没有的记录，同时对于编辑前后都有的记录作出价格，数量，金额，折扣的对比。
      isHas = false;
      newSaleOrderDetails.each do |newSaleOrderDetail|        
        if oldSaleOrderDetail.product_id == newSaleOrderDetail.product_id
          isHas = true;
          if (oldSaleOrderDetail.unit_price == newSaleOrderDetail.unit_price && oldSaleOrderDetail.discount == newSaleOrderDetail.discount && oldSaleOrderDetail.quantity == newSaleOrderDetail.quantity &&  oldSaleOrderDetail.amount == newSaleOrderDetail.amount)
            #如果完全一样没变化，就删掉。
            newSaleOrderDetails = newSaleOrderDetails - [newSaleOrderDetail]
            oldSaleOrderDetails = oldSaleOrderDetails - [oldSaleOrderDetail]
            oldSaleOrderDetail.edit_status = 'same'
          else            
            oldSaleOrderDetail.edit_status = 'edit'
            oldSaleOrderDetail.new_unit_price = newSaleOrderDetail.unit_price
            oldSaleOrderDetail.new_discount = newSaleOrderDetail.discount
            oldSaleOrderDetail.new_quantity = newSaleOrderDetail.quantity
            oldSaleOrderDetail.new_amount = newSaleOrderDetail.amount
            newSaleOrderDetails = newSaleOrderDetails - [newSaleOrderDetail]
          end
          break #直接跳出些循环进行下一次循环
        end
      end

      if isHas == false
        oldSaleOrderDetail.edit_status = 'del'
        difSaleOrderDetails << oldSaleOrderDetail
      else        
        if oldSaleOrderDetail.edit_status != 'same'
          difSaleOrderDetails << oldSaleOrderDetail
        end
      end
      isHas = false
    end
    
    #第二次循环时如果还有记录就已经完全是编辑后新增的记录了。
    newSaleOrderDetails.each do |newSaleOrderDetail|
      newSaleOrderDetail.edit_status = 'add'
      newSaleOrderDetail.new_unit_price = newSaleOrderDetail.unit_price
      newSaleOrderDetail.new_discount = newSaleOrderDetail.discount
      newSaleOrderDetail.new_quantity = newSaleOrderDetail.quantity
      newSaleOrderDetail.new_amount = newSaleOrderDetail.amount

      newSaleOrderDetail.unit_price = 0
      newSaleOrderDetail.discount = 0
      newSaleOrderDetail.quantity = 0
      newSaleOrderDetail.amount = 0
      difSaleOrderDetails << newSaleOrderDetail
    end
    
    jsonStr="{totalProperty:"+difSaleOrderDetails.length.to_s+",root:"+difSaleOrderDetails.to_json(:include=>{:product=>{:only=>[:id,:name,:is_suit,:spec,:number]}})+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #和审批相关的订单
  def getAllSaleOrdersByAuditStatus
    conditionsHash = Hash.new
    paramsHash = Hash.new
    paramsHash[:offset] = params[:start].to_i
    paramsHash[:limit] = params[:limit].to_i
    conditionsStr = ''

    if params[:order_number].to_s != ''
      conditionsStr = 'seq_number in (:order_number)'
      conditionsHash[:order_number] = params[:order_number].split(/,/)
    end

    #这里要把暂存的订单排除在外。默认只选择“申请”状态的
    if params[:auditing_status].nil? || params[:auditing_status] == ""
      if conditionsStr == ''
        conditionsStr = "auditing_status like :auditing_status"
      else
        conditionsStr = conditionsStr + " AND auditing_status like :auditing_status"
      end
      conditionsHash[:auditing_status] = "%#{Constants::APPLY}%"
    else
      if conditionsStr == ''
        conditionsStr = "auditing_status like :auditing_status"
      else
        conditionsStr = conditionsStr + " AND auditing_status like :auditing_status"
      end
      conditionsHash[:auditing_status] = '%'+params[:auditing_status].to_s+'%'
    end

    if params[:customer_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = 'customer_id = :customer_id'
      else
        conditionsStr = conditionsStr+' AND customer_id = :customer_id'
      end
      conditionsHash[:customer_id] = params[:customer_id].to_i
    end

    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = 'action_date >= :begin_date'
      else
        conditionsStr = conditionsStr+' AND action_date >= :begin_date'
      end
      conditionsHash[:begin_date] = params[:begin_date].to_date
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = 'action_date <= :end_date'
      else
        conditionsStr = conditionsStr+' AND action_date <= :end_date'
      end
      conditionsHash[:end_date] = params[:end_date].to_date
    end
    role = Role.find(session[:role_id])
    if role.cross_company == "否"
      if conditionsStr == ''
        conditionsStr = 'company_id = :company_id'
      else
        conditionsStr = conditionsStr+' AND company_id = :company_id'
      end
      conditionsHash[:company_id] = session[:company_id]
    end
    count = SaleOrder.countAllSaleOrders conditionsStr,conditionsHash
    saleOrders = SaleOrder.findAllSaleOrders conditionsStr,conditionsHash,paramsHash

    #jsonStr="{totalProperty:"+count.to_s+",root:"+saleOrders.to_json(:include=>[:employee,:department,:customer,:auditor,:delivery_employee])+"}"
    jsonStr="{totalProperty:"+count.to_s+",root:"+saleOrders.to_json(:include=>{:company=>{:only=>[:id,:name]},:employee=>{:only=>[:id,:name]},:department=>{:only=>[:id,:name]},:customer=>{:only=>[:id,:name]},:auditor=>{:only=>[:id,:name]},:delivery_employee=>{:only=>[:id,:name]},:sale_type=>{:only=>[:id,:name]},:sales_strategy=>{:only=>[:id,:name]}})+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #查询销售订单，合呼要求的，可以转化为出库单。
  def getAllSaleOrdersForDelivery
    conditionsHash = Hash.new
    paramsHash = Hash.new
    paramsHash[:offset] = params[:start].to_i
    paramsHash[:limit] = params[:limit].to_i

    conditionsStr = '(auditing_status not like :auditing_status1 AND auditing_status not like :auditing_status2 AND auditing_status != :auditing_status3 AND auditing_status != :auditing_status4) '
    #在这儿补充一些可以入库的必要条件，比如不能是已经撤消的或申请撤消的。也不能是暂存的。
    conditionsHash[:auditing_status1] = "%#{Constants::APPLY}%"
    conditionsHash[:auditing_status2] = "%#{Constants::PASS_APPLY_CANCEL}%"
    conditionsHash[:auditing_status3] = ""
    conditionsHash[:auditing_status4] = "#{Constants::REJECT_APPLY_ADD}"

    if params[:order_number].to_s != ''
      conditionsStr = conditionsStr + ' AND seq_number in (:order_number)'
      conditionsHash[:order_number] = params[:order_number].split(/,/)
    end

    if params[:delivery_status].to_s != ''
      conditionsStr = conditionsStr + ' AND store_status like :delivery_status'
      conditionsHash[:delivery_status] = '%'+params[:delivery_status].to_s+'%'
    end

    if params[:customer_id].to_s != ''
      conditionsStr = conditionsStr + ' AND customer_id = :customer_id'
      conditionsHash[:customer_id] = params[:customer_id].to_i
    end

    #显示产品
    if params[:product_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = 'sale_order_details.product_id = :product_id'
      else
        conditionsStr = conditionsStr+' AND sale_order_details.product_id = :product_id'
      end
      conditionsHash[:product_id] = params[:product_id].to_i
    end
      
    if params[:begin_date].to_s != ''
      conditionsStr = conditionsStr + ' AND action_date >= :begin_date'
      conditionsHash[:begin_date] = params[:begin_date].to_date
    end

    if params[:end_date].to_s != ''
      conditionsStr = conditionsStr + ' AND action_date <= :end_date'
      conditionsHash[:end_date] = params[:end_date].to_date
    end
    role = Role.find(session[:role_id])
    if role.cross_company == "否"
      if conditionsStr == ''
        conditionsStr = 'company_id = :company_id'
      else
        conditionsStr = conditionsStr+' AND company_id = :company_id'
      end
      conditionsHash[:company_id] = session[:company_id]
    end

    count = SaleOrder.countAllSaleOrders conditionsStr,conditionsHash
    saleOrders = SaleOrder.findAllSaleOrders conditionsStr,conditionsHash,paramsHash

    #jsonStr="{totalProperty:"+count.to_s+",root:"+saleOrders.to_json(:include=>[:employee,:department,:customer,:auditor,:delivery_employee])+"}"
    jsonStr="{totalProperty:"+count.to_s+",root:"+saleOrders.to_json(:include=>{:company=>{:only=>[:id,:name]},:employee=>{:only=>[:id,:name]},:department=>{:only=>[:id,:name]},:customer=>{:only=>[:id,:name]},:auditor=>{:only=>[:id,:name]},:delivery_employee=>{:only=>[:id,:name]},:sales_strategy=>{:only=>[:id,:name]}})+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #更新审批状态，审批销售订单（只审批申请新增状态），对针MESO，可能会输入折扣信息。
  def updateAuditingStatus
    paramsHash = Util.paramsPreprocessToHash(params[:data])
    info = nil
    saleOrder = SaleOrder.find(paramsHash['id'])
    if saleOrder !=nil
      SaleOrder.transaction do
        auditingStatus = paramsHash['auditing_status']
        if(auditingStatus == "是")
          auditingStatus = Constants::PASS_APPLY_ADD
          #products信息
          productsHashArray = paramsHash["products"]

          #在进行从新整理数据之前，先把销售订单的总金额置“0”
          saleOrder.amount = 0
          #针对Meso的要求，销售人员不知道价格，所以在会计审批时会填写入价格。
          productsHashArray.each do |productHash|
            saleOrderDetail = SaleOrderDetail.find(productHash["id"].to_i)
            #saleOrderDetail.unit_price = productHash["unit_price"]
            saleOrderDetail.discount = productHash["discount"]
            saleOrderDetail.amount = productHash["amount"]

            saleOrder.amount = saleOrder.amount + saleOrderDetail.amount #计算订单总金额
            saleOrderDetail.save!
          end
        else
          auditingStatus = Constants::REJECT_APPLY_ADD
        end
        saleOrder.auditing_status = auditingStatus
        saleOrder.auditor_id = session[:employee_id]
        saleOrder.audit_opinion =  paramsHash['audit_opinion']
        saleOrder.auditing_date = Time.now
        saleOrder.save!
        info = "成功"
      end
      #在审批时，要判断一下，当前的销售订单与出库订单的情况。以更新销售订单与出库订单的状态
      diffs = SaleOrder.diff_sale_delivery(saleOrder.id)
      #不等于0有二种情况，一是采购的多了入库的少了，二是入的多采购的少了。不管如何，入库状态都设置为""
      if diffs.length !=0
        saleOrder.store_status = Constants::DELIVERING
      elsif diffs.length ==0
        saleOrder.store_status = Constants::FINISH_DELIVERY
      end
      saleOrder.save!
    else
      info = "失败"
    end
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end


  #创建新的销售订单。
  def create
    paramsHash = Util.paramsPreprocessToHash(params[:data])
    #productHash = paramsHash["products"][0]
    saleOrder = SaleOrder.new
    saleOrder.company_id = session[:company_id]
    saleOrder.department_id = session[:department_id]
    saleOrder.employee_id = session[:employee_id]
    saleOrder.customer_id = paramsHash['customer_id']
    saleOrder.linkman = paramsHash['linkman']
    saleOrder.phone = paramsHash['phone']
    saleOrder.sale_type_id = paramsHash['sale_type_id']
    saleOrder.sales_strategy_id = paramsHash['sales_strategy_id']
    saleOrder.sales_id = paramsHash['sales_id']
    saleOrder.sell_dept_id = paramsHash['sell_dept_id']
    saleOrder.delivery_address = paramsHash['delivery_address']
    saleOrder.delivery_desc = paramsHash['delivery_desc']
    saleOrder.contract_number = paramsHash['contract_number']
    saleOrder.tax_rate = paramsHash['tax_rate']
    saleOrder.action_date = paramsHash['action_date']
    saleOrder.description = paramsHash['description']

    #判断一下当前用户角色是否需求审批
    sysParam =  SysParam.find_by_company_id(session[:company_id])

    #判断是暂存还是提交,结合审批一起处理，如果是暂存，就不处理审批的情形
    if paramsHash['temporary'] == '1' || paramsHash['temporary'] == 1
      saleOrder.deal_with_status = Constants::TEMP_ADD
    else
      if sysParam.audit_for_sale_add == "否"
        saleOrder.auditing_status = Constants::PASS_APPLY_ADD
      else
        saleOrder.auditing_status = Constants::AUDIT_APPLY_ADD        
      end
      saleOrder.deal_with_status = ''
    end

    #判断是否需要折扣审批
    if paramsHash['authentication'] == '1'
      saleOrder.unsafe_discount = '是'
      #如果是非安全折扣，就要审批新增
      saleOrder.auditing_status = Constants::AUDIT_APPLY_ADD
    else
      saleOrder.unsafe_discount = '否'
    end

    #插入products信息
    productsHashArray = paramsHash["products"]
    productsHashArray.each do |productHash|
      #如果不为空则加入
      if productHash["product_id"] !=""
        saleOrderDetail = SaleOrderDetail.new
        saleOrderDetail.product_id = productHash["product_id"]
        saleOrderDetail.quantity = productHash["quantity"]
        saleOrderDetail.unit_price = productHash["unit_price"]
        saleOrderDetail.ori_unit_price = productHash["ori_unit_price"]
        saleOrderDetail.discount = productHash["discount"]
        saleOrderDetail.amount = productHash["amount"]
        saleOrder.amount = saleOrder.amount + saleOrderDetail.amount #计算订单总金额
        saleOrderDetail.description = productHash["description"]
        saleOrder.sale_order_details << saleOrderDetail
      end
    end
    #开始判断是否有首付款产生。    
    if paramsHash['rec_amount'] != "" && paramsHash['rec_amount'] != "0"
      #审批后再添加进去。
      #saleOrder.rec_amount = paramsHash['rec_amount']
      receivable = Receivable.new
      receivable.amount = paramsHash['rec_amount']
      receivable.action_date = Date.today.to_s
      receivable.type_of_payment = paramsHash['payment_type']
      receivable.description = paramsHash['payment_desc']
      receivable.company_id = session[:company_id]
      receivable.department_id = session[:department_id]
      receivable.employee_id = session[:employee_id]
      receivable.customer_id = saleOrder.customer_id
      receivable.auditing_status = Constants::AUDIT_APPLY_ADD
    end

    #saleOrder.save
    #开始事务，确保在流水号正常的情况下，保存销售订单,一但出现问题，则循环执行
    begin
      Sequence.transaction do
        #开始为销售订单取得流水号
        sequence = Sequence.where("company_id = ? AND sub_type = ?",session[:company_id],SaleOrder.name).first
        sequence.seq_no = sequence.seq_no + 1
        saleOrder.seq_number = sequence.seq_no
        saleOrder.order_number = OrderUtil.generate_order_number(sequence.ab, saleOrder.seq_number.to_s, saleOrder.sale_type.ab, session[:department], session[:employee])    
        sequence.save!
        saleOrder.save!
        logger.info !receivable.nil?
        #如果进行了首付，则在此保存首付金额。
        if !receivable.nil?
          receivable.sale_order_id = saleOrder.id
          receivable.save!
        end
      end
    rescue ActiveRecord::StaleObjectError
      #这个异常是说明，你当提交时，有人提前更新了你之前读取的信息，违反乐观锁。要retry
      #这里特别指序号产生表。
      logger.info "somebody using sn before yours.............................................................."
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,"StaleObjectError")
      retry
    rescue ActiveRecord::RecordNotFound
      logger.info "RecordNotFound................................................................................"
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,"RecordNotFound")
    rescue Exception => e
      logger.info "logging......................................................................................."
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
      logger.info e
    end
    
    info = "成功"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end


  #编辑后的保存，只能在暂存或驳回使用此方法保存
  def update
    
    begin
      paramsHash = Util.paramsPreprocessToHash(params[:data])

      #productHash = paramsHash["products"][0]
      saleOrder = SaleOrder.where("id = ? AND company_id = ? AND is_locked <> 1 AND (auditing_status is null OR auditing_status not like ?)",paramsHash['id'],session[:company_id],'%通过%').first
      if saleOrder != nil
        #如果在保存时，有人编辑过，则不能保证
        if saleOrder.lock_version > paramsHash['lock_version'].to_i
          info="有其它用户在您保存之前编辑过此销售订单记录，请从新编辑！"
          result = "{success:true,info:'#{info}'}"
          render :text=>result, :layout=>false and return false
        end
        saleOrder.company_id = session[:company_id]
        saleOrder.department_id = session[:department_id]
        saleOrder.employee_id = session[:employee_id]
        saleOrder.customer_id = paramsHash['customer_id']
        saleOrder.linkman = paramsHash['linkman']
        saleOrder.phone = paramsHash['phone']
        saleOrder.sale_type_id = paramsHash['sale_type_id']
        saleOrder.sales_id = paramsHash['sales_id']
        saleOrder.sell_dept_id = paramsHash['sell_dept_id']
        saleOrder.sales_strategy_id = paramsHash['sales_strategy_id']
        saleOrder.delivery_address = paramsHash['delivery_address']
        saleOrder.delivery_desc = paramsHash['delivery_desc']
        saleOrder.contract_number = paramsHash['contract_number']
        saleOrder.tax_rate = paramsHash['tax_rate']
        saleOrder.action_date = paramsHash['action_date']
        saleOrder.description = paramsHash['description']

        saleOrder.audit_opinion = ''
        saleOrder.auditor_id = ''

        #判断一下当前用户角色是否需求审批
        sysParam =  SysParam.find_by_company_id(session[:company_id])

        #判断是暂存还是提交,结合审批一起处理，如果是暂存，就不处理审批的情形
        if paramsHash['temporary'] == '1' || paramsHash['temporary'] == 1
          saleOrder.deal_with_status = Constants::TEMP_ADD
        else
          if sysParam.audit_for_sale_add == "否"
            saleOrder.auditing_status = Constants::PASS_APPLY_ADD
          else
            saleOrder.auditing_status = Constants::AUDIT_APPLY_ADD
          end
          saleOrder.deal_with_status = ''
        end

        #判断是否需要折扣审批
        if paramsHash['authentication'] == '1'
          saleOrder.unsafe_discount = '是'
        else
          saleOrder.unsafe_discount = '否'
        end
    
        #products信息
        productsHashArray = paramsHash["products"]
        #在这里，要作一个增，删，编辑操作，以前台数据为准。
        #首先各取出前台与后台的ID数组
        fgIdArray = Array.new
        productsHashArray.each do |productHash|
          fgIdArray << productHash["id"].to_i
        end

        bgIdArray = Array.new
        saleOrderDetails = saleOrder.sale_order_details
        saleOrderDetails.each do |saleOrderDetail|
          bgIdArray << saleOrderDetail.id
        end

        idIntersectionArray = fgIdArray & bgIdArray
        addIdArray = fgIdArray - idIntersectionArray
        delIdArray = bgIdArray - idIntersectionArray
        SaleOrderDetail.delete(delIdArray)

        #在进行从新整理数据之前，先把销售订单的总金额置“0”
        saleOrder.amount = 0
      
        productsHashArray.each do |productHash|
          saleOrderDetail = nil
          #只有当用户选择的产品不为空时，才有详细订单。
          if productHash["product_id"] !=""
            #没Id说明是新增加的。
            if productHash["id"] == ""
              saleOrderDetail = SaleOrderDetail.new
            else
              saleOrderDetail = SaleOrderDetail.find(productHash["id"].to_i)           
            end
            saleOrderDetail.product_id = productHash["product_id"]
            saleOrderDetail.quantity = productHash["quantity"]
            saleOrderDetail.unit_price = productHash["unit_price"]
            saleOrderDetail.ori_unit_price = productHash["ori_unit_price"]
            saleOrderDetail.discount = productHash["discount"]
            saleOrderDetail.amount = productHash["amount"]
            saleOrder.amount = saleOrder.amount + saleOrderDetail.amount #计算订单总金额
            saleOrderDetail.description = productHash["description"]
            saleOrder.sale_order_details << saleOrderDetail
          end
        end
        saleOrder.save!
      end
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
    info="成功"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end

  #当销售订单在通过审批后，又一次编辑基本信息时，用此方法。此方法可以不经过审批
  def updateBaseInfoAfterAudit
    #productHash = paramsHash["products"][0]

    saleOrder = SaleOrder.where(["id = ? AND company_id = ? AND is_locked <> 1 AND (auditing_status is null OR (auditing_status not like ? AND auditing_status not like ?))",params[:id],session[:company_id],'%已经撤消%','%申请撤消%']).first

    #如果在保存时，有人编辑过，则不能保证
    if saleOrder.lock_version > params[:lock_version].to_i
      info="有其它用户在您保存之前编辑过此销售订单记录，请从新编辑！"
      result = "{success:true,info:'#{info}'}"
      render :text=>result, :layout=>false and return false
    end
    
    saleOrder.company_id = session[:company_id]
    saleOrder.department_id = session[:department_id]
    saleOrder.employee_id = session[:employee_id]
    saleOrder.customer_id = params[:customer_id]
    saleOrder.linkman = params[:linkman]
    saleOrder.phone = params[:phone]
    #保存基本信息时不能保存销售订单的类型
    #saleOrder.sub_type = params[:sub_type]
    saleOrder.sales_id = params[:sales_id]
    saleOrder.sell_dept_id = params[:sell_dept_id]
    saleOrder.sale_type_id = params[:sale_type_id]
    saleOrder.sales_strategy_id = params[:sales_strategy_id]
    saleOrder.delivery_address = params[:delivery_address]
    saleOrder.delivery_desc = params[:delivery_desc]
    saleOrder.contract_number = params[:contract_number]
    saleOrder.tax_rate = params[:tax_rate]
    saleOrder.action_date = params[:action_date]
    saleOrder.description = params[:description]

    #saleOrder.save

    #开始事务，确保在流水号正常的情况下，保存销售订单,一但出现问题，则循环执行
    begin
      saleOrder.save!
    rescue ActiveRecord::StaleObjectError
      #这个异常是说明，你当提交时，有人提前更新了你之前读取的信息，违反乐观锁。要retry
      #这里特别指序号产生表。
      logger.info "somebody using sn before yours.............................................................."
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,"StaleObjectError")
      retry
    rescue ActiveRecord::RecordNotFound
      logger.info "RecordNotFound................................................................................"
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,"RecordNotFound")
    rescue Exception => e
      logger.info "logging......................................................................................."
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
      logger.info e
    end

    info="成功"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end
  
  #当销售订单在通过审批后，又一次编辑时，用此方法。
  def updateAfterAudit
    
    paramsHash = Util.paramsPreprocessToHash(params[:data])
    #productHash = paramsHash["products"][0]
    #在这儿，只要能查看到此订单的人，就可以编辑，所以暂时去掉了员工Id的处理。
    saleOrder = SaleOrder.where("id = ? AND company_id = ? AND is_locked <> 1 AND (auditing_status is null OR (auditing_status not like ? AND auditing_status not like ?))",paramsHash['id'],session[:company_id],'%已经撤消%','%申请撤消%').first

    #如果在保存时，有人编辑过，则不能保证
    if saleOrder.lock_version > paramsHash['lock_version'].to_i
      info="有其它用户在您保存之前编辑过此销售订单记录，请从新编辑！"
      result = "{success:true,info:'#{info}'}"
      render :text=>result, :layout=>false and return false
    end

    #判断是否需要折扣审批
    if paramsHash['authentication'] == '1'
      saleOrder.unsafe_discount = '是'
    else
      saleOrder.unsafe_discount = '否'
    end
    
    saleOrder.company_id = session[:company_id]
    saleOrder.department_id = session[:department_id]
    saleOrder.employee_id = session[:employee_id]
    saleOrder.customer_id = paramsHash['customer_id']
    saleOrder.linkman = paramsHash['linkman']
    saleOrder.phone = paramsHash['phone']
    saleOrder.sale_type_id = paramsHash['sale_type_id']
    saleOrder.sales_strategy_id = paramsHash['sales_strategy_id']
    saleOrder.sales_id = paramsHash['sales_id']
    saleOrder.sell_dept_id = paramsHash['sell_dept_id']
    saleOrder.delivery_address = paramsHash['delivery_address']
    saleOrder.delivery_desc = paramsHash['delivery_desc']
    saleOrder.contract_number = paramsHash['contract_number']
    saleOrder.tax_rate = paramsHash['tax_rate']
    saleOrder.action_date = paramsHash['action_date']
    saleOrder.description = paramsHash['description']

    #判断一下当前用户角色是否需求审批
    sysParam =  SysParam.find_by_company_id(session[:company_id])

    #判断是暂存还是提交,结合审批一起处理，如果是暂存，就不处理审批的情形

    saleOrder.auditing_status = Constants::AUDIT_APPLY_EDIT
    saleOrder.deal_with_status = ''

    #修改后的总额要先清0
    saleOrder.temp_amount = 0
    
    #插入products信息
    productsHashArray = paramsHash["products"]
    productsHashArray.each do |productHash|
      #如果不为空则加入
      if productHash["product_id"] !=""
        saleOrderDetail = SaleOrderDetail.new
        saleOrderDetail.product_id = productHash["product_id"]
        saleOrderDetail.quantity = productHash["quantity"]
        saleOrderDetail.unit_price = productHash["unit_price"]
        saleOrderDetail.ori_unit_price = productHash["ori_unit_price"]
        saleOrderDetail.discount = productHash["discount"]
        saleOrderDetail.amount = productHash["amount"]
        saleOrder.temp_amount = saleOrder.temp_amount + saleOrderDetail.amount #计算修改后的订单总金额
        saleOrderDetail.description = productHash["description"]
        saleOrderDetail.is_locked = 1  #这里状态全是锁定的。
        saleOrder.sale_order_details << saleOrderDetail
      end
    end
    
    #开始事务，确保在流水号正常的情况下，保存销售订单,一但出现问题，则循环执行
    begin
      SaleOrder.transaction do
        saleOrder.save!
      end
    rescue ActiveRecord::RecordNotFound
      logger.info "RecordNotFound................................................................................"
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,"RecordNotFound")
    rescue Exception => e
      logger.info "logging......................................................................................."
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
      logger.info e
    end

    info="成功"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end

  #新版本的出库订单准备方法将不再针对销售订单中自己的货，可以出所有的货。但前提是相关的总库存不为负数
  def getSaleOrderDetailBalanceForDelivery
    saleOrderId = params[:sale_order_id]
    #order_quantity是未转的数量，不代表可以转，因为有时候库存不足
    #总的思路就是显示可前本销售订单可转数，
    productIds = Array.new
    saleOrderDetails = SaleOrderDetail.where(["sale_order_id = ? AND is_locked = 0",saleOrderId])
    saleOrderDetails.each do |saleOrderDetail|
      productIds << saleOrderDetail.product_id
    end
    saleOrders = SaleOrder.select("sd.id,p.id as product_id,p.name as product_name,p.is_suit,p.unit_price,p.number,p.spec,p.unit,sd.quantity,sd.discount,sd.amount,sd.description,sd.quantity - ifnull(newdd.quantity,0) as order_quantity,case when sd.quantity - ifnull(newdd.quantity,0) > ifnull(pc.stock_quantity,0) then ifnull(pc.stock_quantity,0) else sd.quantity - ifnull(newdd.quantity,0) END as balance_quantity,ifnull(pc.stock_quantity,0) as stock_quantity ").joins("so left join sale_order_details sd on so.id = sd.sale_order_id
left join (select delivery_order_id,d.sale_order_id,product_id,sum(quantity) as quantity
from delivery_orders d left join delivery_order_details dd on d.id = dd.delivery_order_id
where d.sale_order_id = "+saleOrderId+" AND d.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'
group by product_id) newdd on newdd.product_id = sd.product_id
left join products p on p.id = sd.product_id
left join (select product_id,sum(quantity) as stock_quantity from product_collections where is_locked = 0 AND product_id in (#{productIds.join(",")}) group by product_id) pc on pc.product_id = p.id").where("(sd.quantity - ifnull(newdd.quantity,0)) > 0 AND so.id =  "+saleOrderId)

    jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json()+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end


  #得到某销售订单的详细信息，并显示与出库订单之间的数量差比
  def getSaleOrderDetailsBalanceById
    saleOrderId = params[:sale_order_id]
    saleOrders = SaleOrder.select("sd.id,p.id as product_id,p.name as product_name,p.number as product_number,p.is_suit,sd.unit_price,p.number,p.spec,p.unit,sd.quantity,sd.discount,sd.amount,sd.description,newdd.quantity as dd_quantity,(ifnull(newdd.quantity,0) - sd.quantity) as balance_quantity ").joins("so left join sale_order_details sd on so.id = sd.sale_order_id AND sd.is_locked = 0
left join (select delivery_order_id,d.sale_order_id,product_id,sum(quantity) as quantity
from delivery_orders d left join delivery_order_details dd on d.id = dd.delivery_order_id
where d.sale_order_id = "+saleOrderId+" AND d.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'
group by product_id) newdd on newdd.product_id = sd.product_id
left join products p on p.id = sd.product_id
where so.id =  "+saleOrderId)
    if session[:financial_management] == "是"
      jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json()+"}"
    else
      jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json(:except=>[:amount,:discount,:unit_price])+"}"
    end

    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #申请撤消销售订单,如果销售没有被初级审批过，则可以直接设置为撤消液态。
  #对于销售订单是一定要通过审批的
  def applyToCancel
    saleOrder = SaleOrder.find(params[:id])
    
    if (saleOrder.auditing_status.include? "通过") || (saleOrder.auditing_status.include? "驳回")
      saleOrder.auditing_status = Constants::AUDIT_APPLY_CANCEL
      saleOrder.deal_with_status = Constants::AUDIT_APPLY_CANCEL
      #通过状态，要经过审批
    else
      #申请状态，可以直接标记为撤消
      saleOrder.auditing_status = Constants::PASS_APPLY_CANCEL
      saleOrder.deal_with_status = Constants::CANCELED
    end
    saleOrder.auditor_id = session[:employee_id]
    saleOrder.save!
    
    info = "成功"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end

  #根据审批的结果，处理销售订单，如果审批同意，则取消所有入库订单。
  def updateAuditingCancelStatus
    saleOrder = SaleOrder.find(params[:id])
    if params[:auditing_status]=='是'
      saleOrder.auditing_status = Constants::PASS_APPLY_CANCEL
      saleOrder.deal_with_status = Constants::CANCELED
      saleOrder.audit_opinion = params[:audit_opinion]
    else
      saleOrder.audit_opinion = params[:audit_opinion]
      saleOrder.auditing_status = Constants::REJECT_APPLY_CANCEL
    end
    saleOrder.auditor_id = session[:employee_id]
    saleOrder.auditing_date = Time.now
    saleOrder.save!

    info = "成功"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end

  #审批销售订单的变更
  def updateAuditingEditStatus
    saleOrder = SaleOrder.find(params[:id])
    SaleOrder.transaction do
      if params[:auditing_status]=='是'
        saleOrderHx = SaleOrderHx.new
        saleOrderHx.sale_order_id = saleOrder.id
        saleOrderHx.company_id = session[:company_id]
        saleOrderHx.department_id = session[:department_id]
        saleOrderHx.employee_id = session[:employee_id]
        saleOrderHx.amount = saleOrder.amount
        saleOrderHx.save!

        saleOrder.auditing_status = Constants::PASS_APPLY_EDIT
        saleOrder.deal_with_status = "已变更"
        saleOrder.audit_opinion = params[:audit_opinion]
        #开始删除相关的以前的记录
        saleOrder.amount = saleOrder.temp_amount
        #saleOrder.sale_order_details.where(:is_locked=>0).destroy_all
        #saleOrder.sale_order_details.where(:is_locked=>1).update_all(:is_locked=>0)


        oriIdArray = Array.new
        #oriSaleOrderDetails为审批同意前的正式产品
        oriSaleOrderDetails = saleOrder.sale_order_details.where(:is_locked=>0)
        oriSaleOrderDetails.each do |saleOrderDetail|
          oriIdArray << saleOrderDetail.product_id
          #在生成oriIdArray的同时进行旧有订单的历史备份
          saleOrderHxDetail = SaleOrderHxDetail.new
          saleOrderHxDetail.sale_order_hx_id = saleOrderHx.id
          saleOrderHxDetail.sale_order_id = saleOrder.id
          saleOrderHxDetail.product_id = saleOrderDetail.product_id
          saleOrderHxDetail.quantity = saleOrderDetail.quantity
          saleOrderHxDetail.unit_price = saleOrderDetail.unit_price
          saleOrderHxDetail.discount = saleOrderDetail.discount
          saleOrderHxDetail.amount = saleOrderDetail.amount
          saleOrderHxDetail.description = saleOrderDetail.description
          saleOrderHxDetail.save!
        end

        newIdArray = Array.new
        #newSaleOrderDetails为审批同意前的所有产品，如果同意，则这些产品就是正式的产品
        newSaleOrderDetails = saleOrder.sale_order_details.where(:is_locked=>1)
        newSaleOrderDetails.each do |saleOrderDetail|
          newIdArray << saleOrderDetail.product_id
        end

        idIntersectionArray = oriIdArray & newIdArray
        addIdArray = newIdArray - idIntersectionArray
        delIdArray = oriIdArray - idIntersectionArray
        if !delIdArray.empty?
          SaleOrderDetail.delete_all(["product_id in (?) AND sale_order_id = ?" , delIdArray,saleOrder.id])
        end
      
        #把新增的记录，变为正式记录
        if !addIdArray.empty?
          SaleOrderDetail.update_all("is_locked = 0","is_locked = 1 AND sale_order_id = #{saleOrder.id} AND product_id in (#{addIdArray.join(',')})")
        end
        #SaleOrderDetail.delete(delIdArray)
        #开始把新的产品的信息，添加到老的产品信息中。
        newSaleOrderDetails.each do |newSaleOrderDetail|
          oriSaleOrderDetail = SaleOrderDetail.where("sale_order_id = ? AND is_locked = 0 AND product_id = ?",saleOrder.id,newSaleOrderDetail.product_id).first
          oriSaleOrderDetail.quantity = newSaleOrderDetail.quantity
          oriSaleOrderDetail.unit_price = newSaleOrderDetail.unit_price
          oriSaleOrderDetail.discount = newSaleOrderDetail.discount
          oriSaleOrderDetail.amount = newSaleOrderDetail.amount
          oriSaleOrderDetail.description = newSaleOrderDetail.description
          oriSaleOrderDetail.save!
        end
        saleOrder.sale_order_details.where(:is_locked=>1).destroy_all
      else
        saleOrder.audit_opinion = params[:audit_opinion]
        saleOrder.sale_order_details.where(:is_locked=>1).destroy_all
        saleOrder.auditing_status = Constants::REJECT_APPLY_EDIT
      end
      saleOrder.auditor_id = session[:employee_id]
      saleOrder.auditing_date = Time.now
      saleOrder.save!

      #在审批时，要判断一下，当前的销售订单与出库订单的情况。以更新销售订单与出库订单的状态
      diffs = SaleOrder.diff_sale_delivery(saleOrder.id)
      #不等于0有二种情况，一是采购的多了入库的少了，二是入的多采购的少了。不管如何，入库状态都设置为""
      if diffs.length !=0
        saleOrder.store_status = Constants::DELIVERING
      elsif diffs.length ==0
        saleOrder.store_status = Constants::FINISH_DELIVERY
      end
      saleOrder.save!
    end
    info = "成功"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end

  #得到某个销售订单的所有收款
  def getReceivablesBySaleId
    receivables = Receivable.where(["sale_order_id = ?" , params[:id]]).includes([:employee])
    jsonStr = "{totalProperty:"+receivables.length.to_s+",root:"+receivables.to_json(:include=>[:employee])+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #根据销售订单号，模糊查询销售订单
  def getSaleOrderByNumber
    query = params[:query]
    saleOrder = SaleOrder.find_by_seq_number(query,:conditions=>['(auditing_status = ? OR auditing_status = ?) AND deal_with_status = ?', Constants::PASS_APPLY_ADD , Constants::PASS_APPLY_EDIT ,''])
    rootStr = saleOrder.to_json()
    jsonStr = "{root:["+rootStr+"]}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #得到某个销售订单下的所有产品
  def getProductsOfSaleOrder
    start = params[:start]
    limit = params[:limit]
    if start.nil?
      start = 0
    end
    
    if limit.nil?
      limit = 50
    end
    
    if params[:query].nil? || params[:query] == "请选择产品"
      queryStr = ""
    else
      queryStr = params[:query]
    end
    #这里要保证输出saleDetailId！！！！！
    if params[:id] != "0"
      #这里为了采购的方便把可以选择的销售订单中的产品细节，全设置为非套组类别
      #saleOrderDetails = SaleOrderDetail.find_all_by_sale_order_id(params[:id],:conditions=>["is_locked = 0"])
      saleOrderDetails = SaleOrderDetail.filter_by_product_suit params[:id]

      productIdArray = Array.new
      saleOrderDetails.each do |saleOrderDetail|
        productIdArray << saleOrderDetail.product_id
      end
      products = Product.where(["id in (?)",productIdArray])
      count = products.length
    else
      #productsAll = Product.name_like queryStr
      productsAll = Product.name_like_for_purchase queryStr
      count = productsAll.length
      products = productsAll.paginate start,limit
    end
    jsonStr = "{totalProperty:"+count.to_s+",root:"+products.to_json(:only=>[:id,:name,:number,:spec,:unit_price,:unit,:is_suit])+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end
  
  #  def getProductsOfSaleOrder
  #    products = SaleOrderDetail.find_all_by_sale_order_id(params[:id],:include=>[:product],:conditions=>["is_locked = 0"])
  #    jsonStr = "{totalProperty:"+products.length.to_s+",root:"+products.to_json(:include=>[:product])+"}"
  #    #logger.info jsonStr
  #    render :text=>jsonStr, :layout=>false
  #  end

  #得到用于采购的销售订单
  def getSaleOrersForPurchase
    saleOrders = SaleOrder.select("id,order_number").where("(auditing_status != '' AND auditing_status not like ? AND auditing_status not like ?) AND purchase_status != ? AND store_status != ?","%#{Constants::CANCEL}%","%#{Constants::APPLY}%",Constants::FINISH_PURCHASE,Constants::FINISH_ENTRY)
    saleOrder = SaleOrder.new
    saleOrder.id = 0
    saleOrder.order_number = "无销售订单关联"
    saleOrders << saleOrder
    jsonStr = "{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json()+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end


  #得到变更历史的摘要
  def getHxesBySaleId
    saleOrderHxes = SaleOrderHx.where(sale_order_id:params[:id]).includes([:department,:employee])
    jsonStr="{totalProperty:"+saleOrderHxes.length.to_s+",root:"+saleOrderHxes.to_json(:include=>[:department,:employee])+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #根据变更Id得到详细信息
  def getHxDetailsByHxId
    saleOrderHxDetails = SaleOrderHxDetail.where(sale_order_hx_id:params[:id]).includes([:product])
    jsonStr="{totalProperty:"+saleOrderHxDetails.length.to_s+",root:"+saleOrderHxDetails.to_json(:include=>{:product=>{:only=>[:id,:name,:number,:is_suit,:spec]}})+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #销售金额统计，主要参数有，时间段，部门，人员，产品明细（金额，数量）如果没有产品明细，只显示金额
  def getCommonSalesStats
    begin
      #begin
      conditionsStr = ''

      if params[:sell_dept_id].to_s != ''
        conditionsStr = conditionsStr + " s.sell_dept_id = #{params[:sell_dept_id]}"
      end
      
      if params[:sales_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.sales_id = #{params[:sales_id]}"
        else
          conditionsStr = " AND s.sales_id = #{params[:sales_id]}"
        end        
      end

      #处于申请或已撤消状态的订单，不计算在内。
      if conditionsStr == ''
        conditionsStr = " s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      else
        conditionsStr = conditionsStr + " AND s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      end
      
      if params[:begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at >= '#{params[:begin_date].to_date.to_s + ' 00:00:00'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at >= '#{params[:begin_date].to_date.to_s + ' 00:00:00'}'"
        end
      end

      if params[:end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at <= '#{params[:end_date].to_date.to_s + ' 59:59:59'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at <= '#{params[:end_date].to_date.to_s + ' 59:59:59'}'"
        end
      end

      if params[:product_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = " sd.product_id = #{params[:product_id]}"
        else
          conditionsStr = conditionsStr+" AND sd.product_id = #{params[:product_id]}"
        end
      end

      if conditionsStr != ""
        conditionsStr = " where " + conditionsStr
      end    

      groupByStr = ""
      
      if params[:product_info] == "true"
        groupByStr = "group by sd.product_id"
        selectStr = "sum(sd.quantity) as quantity,sum(sd.amount) as amount,p.name as product_name,p.number as product_number "
      else
        selectStr = "sum(sd.quantity) as quantity,sum(sd.amount) as amount "
      end

      if params[:sell_dept_id].to_s == ""
        if params[:dept_group].to_s == 'true'
          if groupByStr == ""
            groupByStr = "group by s.sell_dept_id"
          else
            groupByStr = groupByStr + ",s.sell_dept_id"
          end          
          selectStr = selectStr + ",d.name as sell_dept_name "
        end
      else
        selectStr = selectStr + ",d.name as sell_dept_name "
      end

      if params[:sales_id].to_s == ""
        if params[:sales_group].to_s == 'true'
          if groupByStr == ""
            groupByStr = "group by s.sales_id"
          else
            groupByStr = groupByStr + ",s.sales_id"
          end 
          selectStr = selectStr + ",e.name sales_name "
        end
      else
        selectStr = selectStr + ",e.name sales_name "
      end

      logger.info conditionsStr
      logger.info "s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id " + conditionsStr
      #saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id #{conditionsStr}")
      saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id
                                                                     left join products p on p.id = sd.product_id
                                                                     left join employees e on s.sales_id = e.id
                                                                     left join departments d on d.id = s.sell_dept_id " + conditionsStr + groupByStr)
      
      
      jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json(:include=>[:employee,:department,:customer,:auditor,:delivery_employee,:sales])+"}"
      #logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end

  #设置销售订单的采购处理状态
  def setupPurchaseStatus
    begin
      SaleOrder.transaction do
        saleOrder = SaleOrder.find(params[:id])
        if params[:purchase_status] == "finish"
          saleOrder.purchase_status = Constants::FINISH_PURCHASE
        else
          saleOrder.purchase_status = ""
        end
        saleOrder.save!
      end
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
    info = "完成"
    result = "{success:true,info:'#{info}'}"
    render :text=>result, :layout=>false
  end

  #根据部门统计销售业绩
  def getDeptSalesForReport
    begin
      #begin
      conditionsStr = ''
      if params[:sell_dept_id].to_s != ''
        conditionsStr = conditionsStr + " s.sell_dept_id = #{params[:sell_dept_id]}"
      end

      #处于申请或已撤消状态的订单，不计算在内。
      if conditionsStr == ''
        conditionsStr = " s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      else
        conditionsStr = conditionsStr + " AND s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      end

      if params[:begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        end
      end

      if params[:end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        end
      end

      if params[:product_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = " sd.product_id = #{params[:product_id]}"
        else
          conditionsStr = conditionsStr+" AND sd.product_id = #{params[:product_id]}"
        end
      end

      if conditionsStr != ""
        conditionsStr = " where " + conditionsStr
      end

      selectStr = "sum(sd.amount) as amount,d.name as sell_dept_name"
      groupByStr = ""
      if params[:time_slot] == 'none'
        groupByStr = "group by sell_dept_id order by amount DESC"
      elsif params[:time_slot] == 'day'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m-%d') as time_slot"
        groupByStr = "group by sell_dept_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'month'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m') as time_slot"
        groupByStr = "group by sell_dept_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'year'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y') as time_slot"
        groupByStr = "group by sell_dept_id,time_slot order by amount DESC"
      end
       

      logger.info conditionsStr
      logger.info "s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id " + conditionsStr
      #saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id #{conditionsStr}")
      if params[:sell_dept_id] == ''
        saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id
                                                                     left join products p on p.id = sd.product_id
                                                                     left join departments d on d.id = s.sell_dept_id " + conditionsStr + groupByStr)
      else
        saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id
                                                                     left join products p on p.id = sd.product_id
                                                                     left join departments d on d.id = s.sell_dept_id " + conditionsStr)
      end

      jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json(:include=>[:department])+"}"
      #logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end

  #根据员工统计销售业绩
  def getSalesSalesForReport
    begin
      #begin
      conditionsStr = ''
      if params[:sales_id].to_s != ''
        conditionsStr = conditionsStr + " s.sales_id = #{params[:sales_id]}"
      end

      #处于申请或已撤消状态的订单，不计算在内。
      if conditionsStr == ''
        conditionsStr = " s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      else
        conditionsStr = conditionsStr + " AND s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      end

      if params[:begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        end
      end

      if params[:end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        end
      end

      if params[:product_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = " sd.product_id = #{params[:product_id]}"
        else
          conditionsStr = conditionsStr+" AND sd.product_id = #{params[:product_id]}"
        end
      end

      if conditionsStr != ""
        conditionsStr = " where " + conditionsStr
      end

      selectStr = "sum(sd.amount) as amount,e.name as sales_name"
      groupByStr = ""
      if params[:time_slot] == 'none'
        groupByStr = "group by sales_id order by amount DESC"
      elsif params[:time_slot] == 'day'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m-%d') as time_slot"
        groupByStr = "group by sales_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'month'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m') as time_slot"
        groupByStr = "group by sales_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'year'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y') as time_slot"
        groupByStr = "group by sales_id,time_slot order by amount DESC"
      end


      logger.info conditionsStr
      logger.info "s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id " + conditionsStr
      #saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id #{conditionsStr}")
      if params[:sales_id] == ''
        saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id
                                                                     left join products p on p.id = sd.product_id
                                                                     left join employees e on e.id = s.sales_id " + conditionsStr + groupByStr)
      else
        saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id
                                                                     left join products p on p.id = sd.product_id
                                                                     left join employees e on e.id = s.sales_id " + conditionsStr)
      end

      jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json(:include=>[:department])+"}"
      #logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end

  #统计产品的销量
  def getProductSalesVolumeForReport
    begin
      #begin
      conditionsStr = ''

      #处于申请或已撤消状态的订单，不计算在内。
      if conditionsStr == ''
        conditionsStr = " s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      else
        conditionsStr = conditionsStr + " AND s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      end

      if params[:begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        end
      end

      if params[:end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        end
      end

      if conditionsStr != ""
        conditionsStr = " where " + conditionsStr
      end

      selectStr = "sum(sd.quantity) as quantity,p.name as product_name,p.number as product_number,sum(sd.amount)/sum(quantity) as unit_price "
      groupByStr = ""
      if params[:time_slot] == 'none'
        groupByStr = "group by product_id order by quantity DESC"
      elsif params[:time_slot] == 'day'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m-%d') as time_slot"
        groupByStr = "group by product_id,time_slot order by quantity DESC"
      elsif params[:time_slot] == 'month'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m') as time_slot"
        groupByStr = "group by product_id,time_slot order by quantity DESC"
      elsif params[:time_slot] == 'year'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y') as time_slot"
        groupByStr = "group by product_id,time_slot order by quantity DESC"
      end


      logger.info conditionsStr
      logger.info "s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id " + conditionsStr
      #saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id #{conditionsStr}")

      saleOrders = SaleOrder.all(:limit => 100,:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id
                                                                     left join products p on p.id = sd.product_id" + conditionsStr + groupByStr)


      jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json()+"}"
      #logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end

  #统计产品的销售额
  def getProductSalesAmountForReport
    begin
      #begin
      conditionsStr = ''

      #处于申请或已撤消状态的订单，不计算在内。
      if conditionsStr == ''
        conditionsStr = " s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      else
        conditionsStr = conditionsStr + " AND s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      end

      if params[:begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        end
      end

      if params[:end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        end
      end

      if conditionsStr != ""
        conditionsStr = " where " + conditionsStr
      end

      selectStr = "sum(sd.amount) as amount,sum(sd.amount)/sum(quantity) as unit_price, p.name as product_name, p.number as product_number"
      groupByStr = ""
      if params[:time_slot] == 'none'
        groupByStr = "group by product_id order by amount DESC"
      elsif params[:time_slot] == 'day'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m-%d') as time_slot"
        groupByStr = "group by product_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'month'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m') as time_slot"
        groupByStr = "group by product_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'year'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y') as time_slot"
        groupByStr = "group by product_id,time_slot order by amount DESC"
      end


      logger.info conditionsStr
      logger.info "s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id " + conditionsStr
      #saleOrders = SaleOrder.all(:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id left join products p on p.id = sd.product_id #{conditionsStr}")

      saleOrders = SaleOrder.all(:limit => 100,:select=>selectStr,:joins=>"s left join sale_order_details sd on sd.sale_order_id = s.id
                                                                     left join products p on p.id = sd.product_id" + conditionsStr + groupByStr)


      jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json()+"}"
      #logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end

  #统计不同销售策略下的销售额
  def getSalesStrategyAmountForReport
    begin
      #begin
      conditionsStr = ''

      #处于申请或已撤消状态的订单，不计算在内。
      if conditionsStr == ''
        conditionsStr = " s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      else
        conditionsStr = conditionsStr + " AND s.auditing_status not like '%申请%' AND s.auditing_status not like '%#{Constants::PASS_APPLY_CANCEL}%' "
      end

      if params[:customer_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.customer_id = #{params[:customer_id]} "
        else
          conditionsStr = conditionsStr+" AND s.customer_id = #{params[:customer_id]} "
        end
      end
      
      if params[:sales_strategy_id].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.sales_strategy_id = #{params[:sales_strategy_id]} "
        else
          conditionsStr = conditionsStr+" AND s.sales_strategy_id = #{params[:sales_strategy_id]} "
        end
      end
      
      if params[:begin_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at >= '#{params[:begin_date] + ' 00:00:00'}'"
        end
      end

      if params[:end_date].to_s != ''
        if conditionsStr == ''
          conditionsStr = " s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        else
          conditionsStr = conditionsStr+" AND s.created_at <= '#{params[:end_date] + ' 59:59:59'}'"
        end
      end

      if conditionsStr != ""
        conditionsStr = " where " + conditionsStr
      end

      selectStr = "sum(s.amount) as amount,ss.name sales_strategy_name,c.name customer_name"
      groupByStr = ""
      if params[:time_slot] == 'none'
        groupByStr = "group by customer_id,sales_strategy_id order by amount DESC"
      elsif params[:time_slot] == 'day'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m-%d') as time_slot"
        groupByStr = "group by customer_id,sales_strategy_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'month'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y-%m') as time_slot"
        groupByStr = "group by customer_id,sales_strategy_id,time_slot order by amount DESC"
      elsif params[:time_slot] == 'year'
        selectStr = selectStr + " ,DATE_FORMAT(s.created_at,'%Y') as time_slot"
        groupByStr = "group by customer_id,sales_strategy_id,time_slot order by amount DESC"
      end

      logger.info conditionsStr
      
      saleOrders = SaleOrder.all(:limit => 100,:select=>selectStr,:joins=>"s left join customers c on s.customer_id = c.id left join sales_strategies ss on s.sales_strategy_id = ss.id" + conditionsStr + groupByStr)
      sumAmount = 0
      saleOrders.each do |saleOrder|
        sumAmount += saleOrder.amount.to_f
      end
    
      jsonStr="{totalProperty:"+saleOrders.length.to_s+",sum_amount:#{sumAmount},root:"+saleOrders.to_json()+"}"
      #logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end
  
  #销售与出库产品对比分析
  def getProductNeedDelivery
    #begin
    paramsHash = Hash.new
    paramsHash[:offset] = params[:start].to_i
    paramsHash[:limit] = params[:limit].to_i
    conditionsStr = ''

    if params[:sale_order_number].to_s != ''
      conditionsStr = conditionsStr + "so.seq_number in (#{params[:sale_order_number]})"
    end

    if params[:auditing_status].to_s != '' && params[:auditing_status] != ' '
      if conditionsStr == ''
        conditionsStr = "so.auditing_status like '%#{params[:auditing_status].to_s}%'"
      else
        conditionsStr = conditionsStr+" AND so.auditing_status like '%#{params[:auditing_status].to_s}%'"
      end
    end

    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "so.action_date >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND so.action_date >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "so.action_date <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND so.action_date <= '#{params[:end_date]}'"
      end
    end

    if params[:customer_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "c.id = #{params[:customer_id]}"
      else
        conditionsStr = conditionsStr+" AND c.id = #{params[:customer_id]}"
      end
    end

    if params[:product_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "sd.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND sd.product_id = #{params[:product_id]}"
      end
    end


    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr
    end

    if params[:delivery_info] == "true"
      #要对where再处理一下。以不显示数量为0即没有进货的入库详细表信息
      #      if conditionsStr != ""
      #        conditionsStr = conditionsStr + "AND ed.quantity is not null"
      #      else
      #        conditionsStr = "where ed.quantity is not null"
      #      end
      
      saleOrders = SaleOrder.all(:select=>"so.id as sale_order_id,so.auditing_status,dd.auditing_status delivery_auditing_status,so.order_number as sale_order_number,dd.order_number as delivery_order_number,so.action_date ,sd.quantity as sale_quantity,p.name as product_name,p.number as product_number,ifnull(dd.quantity,0) as delivery_quantity ,d.name as department_name,so.store_status,c.name as customer_name",:joins =>"so
left join sale_order_details sd on so.id = sd.sale_order_id AND sd.is_locked = 0
left join (select do.order_number,do.auditing_status,dd.sale_order_id,dd.delivery_order_id,dd.product_id,sum(dd.quantity) quantity from delivery_orders do
           left join delivery_order_details dd on do.id = dd.delivery_order_id where do.auditing_status != '#{Constants::PASS_APPLY_CANCEL}' group by dd.id) dd
           on sd.sale_order_id = dd.sale_order_id AND sd.product_id = dd.product_id AND sd.sale_order_id = dd.sale_order_id
left join products p on p.id = sd.product_id
left join departments d on d.id = so.department_id
left join customers c on c.id = so.customer_id
#{conditionsStr}")
    else
      saleOrders = SaleOrder.all(:select=>"so.id as sale_order_id,so.auditing_status,dd.auditing_status delivery_auditing_status,so.order_number as sale_order_number,so.action_date,sd.quantity as sale_quantity,p.name as product_name,p.number as product_number,ifnull(dd.quantity,0) as delivery_quantity ,d.name as department_name,so.store_status,c.name as customer_name",:joins =>"so
left join sale_order_details sd on so.id = sd.sale_order_id AND sd.is_locked = 0
left join (select dd.sale_order_id,do.auditing_status,dd.delivery_order_id,dd.product_id,sum(dd.quantity) quantity from delivery_orders do
           left join delivery_order_details dd on do.id = dd.delivery_order_id where do.auditing_status != '#{Constants::PASS_APPLY_CANCEL}' group by dd.sale_order_id,dd.sale_order_id,dd.product_id) dd
           on sd.sale_order_id = dd.sale_order_id AND sd.product_id = dd.product_id AND sd.sale_order_id = dd.sale_order_id
left join products p on p.id = sd.product_id
left join departments d on d.id = so.department_id
left join customers c on c.id = so.customer_id
#{conditionsStr} group by sd.id,p.id")
    end
    jsonStr = saleOrders.to_json

    jsonStr="{totalProperty:"+saleOrders.length.to_s+",root:"+saleOrders.to_json()+"}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end

  #生成打印销售凭证的头部
  def sale_order_print_header
    @saleOrder = SaleOrder.find(params[:id],:include=>[:sell_department,:sales,:customer])
  end

  #生成打印销售凭证的内容
  def sale_order_print_content
    @saleOrder = SaleOrder.find(params[:id],:include=>[:sale_order_details,:sales,:customer])
    @saleOrderDetails = @saleOrder.sale_order_details
    @totalQuantity = @saleOrderDetails.sum(:quantity)
    logger.info @totalQuantity
  end

  def sale_order_print_footer
    @saleOrder = SaleOrder.find(params[:id],:include=>[:sale_order_details,:sales,:customer])
  end

  #把相关的销售订单导出为Excel
  def exportDeliveryOrderToXLS
    
    deliveryOrders = DeliveryOrder.where("sale_order_id = ? AND company_id = ? AND auditing_status not like ? AND auditing_status not like ?",params[:id],session[:company_id],"%#{Constants::APPLY}%","%#{Constants::CANCEL}%").includes(:delivery_order_details)
    
    #判断是输出报表，还是输出Json

    require 'spreadsheet'
    Spreadsheet.client_encoding = "UTF-8"
    bookString = StringIO.new
    book = Spreadsheet.open "#{Rails.root.to_s}/report_template/sale_delivery_order.xls"
    i = 4
    sheet1 = book.worksheet 0
    #把相关的销售订单号写上。
    if deliveryOrders.length > 0
      saleOrder = SaleOrder.where("id = ?",deliveryOrders[0].sale_order_id).first
      sheet1[1,0] = saleOrder.order_number
    end
    sheet1[3,0] = "销售出库单号"
    sheet1[3,1] = "产品"
    sheet1[3,2] = "编号"
    sheet1[3,3] = "数量"
    sheet1[3,4] = "销售单价"
    sheet1[3,5] = "折扣"
    sheet1[3,6] = "销售金额"
    sheet1[3,7] = "成本单价"
    sheet1[3,8] = "成本总额"
    sheet1[3,9] = "成本单价(含税)"
    sheet1[3,10] = "成本总额(含税)"
    sheet1[3,11] = "备注"

    deliveryOrders.each do |deliveryOrder|
      calYear = deliveryOrder.updated_at.strftime("%Y").to_i
      calMonth = deliveryOrder.updated_at.strftime("%m").to_i
      deliveryOrderDetails = DeliveryOrderDetail.joins(" dod left outer join cost_prices cp on dod.product_id = cp.product_id left outer join sale_order_details sod on sod.product_id = dod.product_id AND sod.is_locked=0").where("cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth} AND cp.company_id = #{session[:company_id]} AND dod.delivery_order_id = #{deliveryOrder.id} AND sod.sale_order_id = #{deliveryOrder.sale_order_id}").select("dod.id,dod.description,dod.product_id,sod.unit_price,sod.discount,dod.quantity,ROUND(dod.quantity*sod.unit_price*sod.discount,2) amount,cp.cost_price,cp.tax_price,ROUND(dod.quantity*cp.cost_price,2) cost_amount,dod.quantity*cp.tax_price tax_amount").includes(:product)
      #总成本价，总成本价（含税）
      totalCostAmount = 0
      totalTaxAmount = 0
      deliveryOrderDetails.each do |deliveryOrderDetail|
        sheet1[i,0] = deliveryOrder.order_number
        sheet1[i,1] = deliveryOrderDetail.product.name
        sheet1[i,2] = deliveryOrderDetail.product.number
        sheet1[i,3] = deliveryOrderDetail.quantity
        sheet1[i,4] = deliveryOrderDetail.unit_price
        sheet1[i,5] = deliveryOrderDetail.discount
        sheet1[i,6] = deliveryOrderDetail.amount
        sheet1[i,7] = deliveryOrderDetail.cost_price.to_d
        sheet1[i,8] = deliveryOrderDetail.cost_amount.to_f
        sheet1[i,9] = deliveryOrderDetail.tax_price.to_f
        sheet1[i,10] = deliveryOrderDetail.tax_amount.to_f
        sheet1[i,11] = deliveryOrderDetail.description
        totalCostAmount += deliveryOrderDetail.cost_amount.to_f
        totalTaxAmount += deliveryOrderDetail.tax_amount.to_f
        i += 1
      end
      sheet1[i,8] = totalCostAmount
      sheet1[i,10] = totalTaxAmount
      
      i += 1
    end
      

    book.write bookString
    send_data bookString.string,:filename => '销售订单相关出库单明细.xls'
  end

  #得到某个客户的某种类配赠总的消费金额,这里面，还要计算上客户的退货数据。
  def getSumResidualPaymentConsumeByCustomerIdStrategyId
    saleOrders = SaleOrder.where("customer_id = ? AND sales_strategy_id = ? AND auditing_status not like ? AND auditing_status not like ?",params[:customer_id].to_i,params[:sales_strategy_id].to_i,Constants::APPLY,Constants::PASS_APPLY_CANCEL).select("sum(amount) amount")
    sumSaleAmount = 0
    if saleOrders[0].amount.nil?
      sumSaleAmount = 0
    else
      sumSaleAmount = saleOrders[0].amount
    end
    logger.info sumSaleAmount
    #计算退货的金额
    saleReturneds = SaleReturned.joins("sr left outer join sale_orders so on sr.sale_order_id = so.id ").where("sr.customer_id = ? AND so.sales_strategy_id = ? AND sr.auditing_status not like ?",params[:customer_id].to_i,params[:sales_strategy_id].to_i,Constants::PASS_APPLY_CANCEL).select("sum(sr.amount) amount")
    if saleReturneds[0].amount.nil?
      sumReturnedAmount = 0
    else
      sumReturnedAmount = saleReturneds[0].amount
    end

    sumSaleAmount = sumSaleAmount - sumReturnedAmount
    jsonStr="{sum_amount:#{sumSaleAmount}}"
    #logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end
end
